if exists (select 1 from sysobjects where name = 'get_planrowcolor' and type = 'P')
begin
   drop procedure get_planrowcolor
   print 'Procedure: get_planrowcolor deleted ...'
end
go
create procedure get_planrowcolor(
  @planid            int = 9,
  @prsschemaid       int = 1
)
as
begin
  set nocount on

   declare @stundenprotag int
   declare @prsid int
   declare @abteilungid int

   create table #dienste(
     datum    datetime,
     rgb      int,
     prsid    int
   )

   select @stundenprotag = stundenprotag
     from PlanSchema ps
    inner join ArbeitsPlan ap
       on ap.SchemaID = ps.SchemaID
    inner join Abteilung a
       on a.AbteilungID = ps.AbteilungID
    where ap.PlanID = @planid
      and ps.PrsSchemaID = @prsschemaid

   select @abteilungid = AbteilungID
     from PlanSchema ps
    inner join ArbeitsPlan ap
       on ap.SchemaID = ps.SchemaID
    where ap.PlanID = @planid
      and ps.PrsSchemaID = @prsschemaid

   select @prsid = PrsID
     from PlanSchema ps
    inner join ArbeitsPlan ap
       on ap.SchemaID = ps.SchemaID
    where ap.PlanID = @planid
      and ps.PrsSchemaID = @prsschemaid

   insert #dienste
   select datum    = Datum,
          rgb      = ARGB,
          prsid    = @prsid
     from ColorPlan
    where PlanID = @planid
      and PrsID = @prsid
      and AbteilungID = @abteilungid

select * from #dienste
   --select datum  = isnull(#dienste.datum,''),
   --       rgb    = isnull(#dienste.rgb,0),
   --       rowrgb = PlanSchema.RGB
   --  from PlanSchema
   -- inner join ArbeitsPlan ap
   --    on ap.SchemaID = PlanSchema.SchemaID
   -- left outer join #dienste
   --    on PlanSchema.PrsID = #dienste.prsid
   -- where PlanSchema.PrsSchemaID = @prsschemaid
   --   and PlanSchema.AbteilungID = @abteilungid
   --   and ap.PlanID = @planid


end
go
print 'Procedure: get_planrowcolor done ...'
go

grant exec on get_planrowcolor to prsadmins with grant option
go
grant exec on get_planrowcolor to prsusers
go

